package com.wuzheng.wisgo.sanitation.cloud.repository.builder.plan;

import com.wuzheng.wisgo.sanitation.cloud.constant.Constants;
import com.wuzheng.wisgo.sanitation.cloud.model.plan.param.TimeSlotParam;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * @author：hoipang
 * @date：Created in 2019-12-19
 * @description：
 * @modified By：
 * @version: 1.0
 */
public class JobPlanBuilder {

    public String buildConditionOfJobPlanDTOSql(String name, String code) {
        return new SQL() {
            {
                SELECT("jp.id, jp.job_name AS planName,jp.job_type_id AS jobTypeCode,jp.job_type AS jobType");
                SELECT("array_to_string(ARRAY(SELECT jpr.road_name FROM ba_job_plan_road AS jpr WHERE jpr.plan_id = jp.id), ',') AS roadName");
                SELECT("array_to_string(ARRAY(SELECT jpv.veh_code FROM ba_job_plan_vehicle AS jpv WHERE jpv.plan_id = jp.id), ',') AS vehCode");
                SELECT("array_to_string(ARRAY(SELECT concat(js.start_work_time,'-',js.off_work_time) FROM ba_job_plan_shift AS js WHERE js.plan_id = jp.id), ',') AS jobPeriod");
                SELECT("jp.job_frequency_id AS jobFrequencyCode, jp.job_frequency AS jobFrequency, jp.remark");
                FROM("ba_job_plan AS jp");
            }
        }.toString();
    }

    public String buildJobPlanRouteDTOSql(String code) {
        return new SQL() {
            {
                switch (code) {
                    case Constants.JobType.TRANSFER:
                        //转运道路sql
                        SELECT("DISTINCT tr.id,tr.route_name AS roadName");
                        FROM("ba_transfer_route AS tr");
                        JOIN("ba_transfer_route_trash AS trt ON tr.id = trt.transfer_route_id AND tr.de_flag = FALSE AND trt.de_flag = FALSE");
                        JOIN("ba_facility AS bf ON bf.id = trt.trash_id AND bf.de_flag = FALSE");
                        WHERE("tr.id NOT IN (SELECT jpt.route_id FROM ba_job_plan_transfer AS jpt)");
                        break;
                    case Constants.JobType.FULL:
                        //作业道路sql
                        SELECT("jr.id,jr.road_name AS roadName");
                        FROM("ba_job_road AS jr");
                        WHERE("jr.id NOT IN (SELECT jpr.road_id FROM ba_job_plan_road AS jpr)");
                        break;
                    default:
                        //作业道路sql
                        SELECT("jr.id,jr.road_name AS roadName");
                        FROM("ba_job_road AS jr");
                        WHERE("jr.id NOT IN (SELECT jpr.road_id FROM ba_job_plan_road AS jpr JOIN ba_job_plan AS jp on jpr.plan_id = jp.id WHERE jp.job_type_id = '" + Constants.JobType.FULL + "' OR jp.job_type_id = #{code})");
                        break;
                }
            }
        }.toString();
    }

    public String buildJobPlanRoadItemSql(String code) {
        return new SQL() {
            {
                switch (code) {
                    case Constants.JobType.TRANSFER:
                        SELECT("tr.id,tr.route_name AS roadName,trt.trash_id AS trashId,bf.longitude,bf.latitude,bf.threshold");
                        FROM("ba_transfer_route AS tr");
                        JOIN("ba_transfer_route_trash AS trt ON tr.id = trt.transfer_route_id AND tr.de_flag = FALSE AND trt.de_flag = FALSE");
                        JOIN("ba_facility AS bf ON bf.id = trt.trash_id AND bf.de_flag = FALSE");
                        WHERE("tr.id NOT IN (SELECT jpt.route_id FROM ba_job_plan_transfer AS jpt)");
                        break;
                    default:
                        SELECT("jr.id,jr.road_name AS roadName");
                        FROM("ba_job_road AS jr");
                        WHERE("jr.id NOT IN (SELECT jpr.road_id FROM ba_job_plan_road AS jpr)");
                        break;
                }
            }
        }.toString();
    }

    public String buildJobPlanVehicleDTOSql(String code, List<TimeSlotParam> timeSlots) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0, len = timeSlots.size(); i < len; i++) {
            sb.append("((jps.start_work_time<='");
            sb.append(timeSlots.get(i).getStartTime());
            sb.append("' AND jps.off_work_time>'");
            sb.append(timeSlots.get(i).getStartTime());
            sb.append("') OR (jps.start_work_time<'");
            sb.append(timeSlots.get(i).getEndTime());
            sb.append("' AND jps.off_work_time>='");
            sb.append(timeSlots.get(i).getEndTime());
            sb.append("') OR (jps.start_work_time>='");
            sb.append(timeSlots.get(i).getStartTime());
            sb.append("' AND jps.off_work_time<='");
            sb.append(timeSlots.get(i).getEndTime());
            sb.append("'))");
            if (i != (len - 1)) {
                sb.append(" OR ");
            }
        }
        return new SQL() {
            {
                SELECT("bv.veh_vin_code AS vinCode, bv.veh_code AS vehCode, bv.veh_use_sort_name AS useTypeName");
                FROM("ba_vehicle AS bv");
                switch (code) {
                    case Constants.JobType.TRANSFER:
                        WHERE("bv.veh_vin_code NOT IN (\n" +
                                "SELECT DISTINCT jpv.veh_vin_code\n" +
                                "FROM ba_job_plan AS jp\n" +
                                "JOIN ba_job_plan_transfer AS jpt ON jp.id = jpt.plan_id\n" +
                                "JOIN ba_job_plan_shift AS jps ON jp.id = jps.plan_id\n" +
                                "JOIN ba_job_plan_vehicle AS jpv ON jp.id = jpv.plan_id\n" +
                                "WHERE ".concat(sb.toString()).concat(")"));
                        break;
                    default:
                        WHERE("bv.veh_vin_code NOT IN (\n" +
                                "SELECT DISTINCT jpv.veh_vin_code\n" +
                                "FROM ba_job_plan AS jp\n" +
                                "JOIN ba_job_plan_road AS jpr ON jp.id = jpr.plan_id\n" +
                                "JOIN ba_job_plan_shift AS jps ON jp.id = jps.plan_id\n" +
                                "JOIN ba_job_plan_vehicle AS jpv ON jp.id = jpv.plan_id\n" +
                                "WHERE ".concat(sb.toString()).concat(")"));
                        break;
                }
                switch (code) {
                    case Constants.JobType.FULL:
                        WHERE("bv.veh_use_sort_id NOT IN ('".concat(Constants.VehicleUseType.TRANSFER).concat("')"));
                        break;
                    case Constants.JobType.CLEAN:
                        WHERE("bv.veh_use_sort_id = '".concat(Constants.VehicleUseType.CLEAN).concat("'"));
                        break;
                    case Constants.JobType.WATER:
                        WHERE("bv.veh_use_sort_id = '".concat(Constants.VehicleUseType.WATER).concat("'"));
                        break;
                    default:
                        WHERE("bv.veh_use_sort_id = '".concat(Constants.VehicleUseType.TRANSFER).concat("'"));
                        break;
                }
            }
        }.toString();
    }

    public String buildJobPlanDetailDTOSql(String id, String code) {
        return new SQL() {
            {
                SELECT("jp.id,jp.job_name AS plan_name,jp.job_type_id AS job_type_code");
                SELECT("array_to_string(ARRAY(SELECT jps.shift_id FROM ba_job_plan_shift AS jps WHERE jps.plan_id = jp.id AND jps.de_flag = FALSE),',') AS shift_ids");
                SELECT("array_to_string(ARRAY(SELECT jpv.veh_vin_code FROM ba_job_plan_vehicle AS jpv WHERE jpv.plan_id = jp.id AND jpv.de_flag = FALSE),',') AS vin_codes");
                switch (code) {
                    case Constants.JobType.TRANSFER:
                        SELECT("array_to_string(ARRAY(SELECT jpt.route_id FROM ba_job_plan_transfer AS jpt WHERE jpt.plan_id = jp.id AND jpt.de_flag = FALSE), ',') AS road_ids");
                        break;
                    default:
                        SELECT("array_to_string(ARRAY(SELECT jpr.road_id FROM ba_job_plan_road AS jpr WHERE jpr.plan_id = jp.id AND jpr.de_flag = FALSE), ',') AS road_ids");
                        break;
                }
                SELECT("jp.job_frequency_id AS job_frequency_code,jp.remark");
                FROM("ba_job_plan AS jp");
                WHERE("jp.de_flag = FALSE AND jp.id = #{id}");
            }
        }.toString();
    }
}
